#!/usr/bin/python
# encoding=utf-8
'''
pip install PyMySQL

https://pymysql.readthedocs.io/en/latest/
'''
import time
import pymysql

connection = pymysql.connect(host='localhost',
                             user='root',
                             password='abcd4321',
                             db='my-all-in',
                             charset='utf8mb4',
                             autocommit=True)

try:
    with connection.cursor() as cursor:
        while True:
            cursor.execute('''
                select count(1) from t_job_task where type = 'YBC_AUTH_ID' and status in ('ING', 'NEW')
            ''')
            qty = cursor.fetchone()[0]
            print('qty: ' + str(qty))
            if qty <= 3:
                cursor.execute('''
                    truncate table t_ing_job_task_1
                ''')
                cursor.execute('''
                    insert into t_ing_job_task_1(biz) 
                    select biz from t_job_task where status in ('ING', 'NEW')
                ''')
                i_rows = cursor.execute(
                    '''
                        insert into t_job_task(type, biz, status, done)
                        select
                            'YBC_AUTH_ID', concat('{"mobile":"', mobile , '"}'), 'NEW', 0
                        from
                            t_ybc_account
                        where
                            idStatus is null
                        and insertTime < NOW() - INTERVAL 5 MINUTE
                        and registerResCode = 200
                        and ((type = 4 and mod(mobile, 2) = 0) or type in (1,2,3) or promotionCode is null)
                        and concat('{"mobile":"', mobile , '"}') not in (select biz from t_ing_job_task_1)
                        order by insertTime
                        limit 1
                    '''
                )
                print("insert rows: ", i_rows)
            time.sleep(0.5)
finally:
    connection.close()
